import sqlite3
from typing import List, Optional, Dict
from decimal import Decimal
from datetime import datetime
from core.models import Order, Trade
from storage.storage_interface import StorageInterface
import decimal

class SQLite3Storage(StorageInterface):
    def __init__(self, db_path: str):
        self.db_path = db_path
        self._init_db()

    def _get_conn(self):
        return sqlite3.connect(self.db_path)

    def _init_db(self):
        conn = self._get_conn()
        c = conn.cursor()
        # 创建orders表，数量相关字段为REAL
        c.execute('''
        CREATE TABLE IF NOT EXISTS orders (
            order_id TEXT UNIQUE,
            symbol TEXT,
            side TEXT,
            order_type TEXT,
            quantity REAL,
            filled REAL,
            quantity_left REAL,
            limit_price REAL,
            stop_price REAL,
            avg_fill_price REAL,
            status TEXT,
            open_time TEXT,
            close_time TEXT,
            duration TEXT,
            commission REAL,
            expiration TEXT,
            multiplier REAL
        )''')
        # 创建trades表，数量相关字段为REAL，顺序与 Trade dataclass 保持一致
        c.execute('''
        CREATE TABLE IF NOT EXISTS trades (
            trade_id TEXT UNIQUE,
            symbol TEXT,
            open_datetime TEXT,
            open_price REAL,
            open_qty REAL,
            close_datetime TEXT,
            close_price REAL,
            close_qty REAL,
            direction TEXT,
            open_order_type TEXT,
            trade_type TEXT,
            point_profit_per_lot REAL,
            total_points REAL,
            gross_profit REAL,
            net_profit REAL,
            commission REAL,
            holding_time TEXT,
            nth_trade INTEGER,
            session TEXT,
            max_floating_profit_points REAL,
            max_floating_profit_dollars REAL,
            max_floating_loss_points REAL,
            max_floating_loss_dollars REAL,
            entry_reason TEXT,
            exit_reason TEXT,
            stop_loss_price REAL,
            stop_loss_reason TEXT,
            take_profit_price REAL,
            take_profit_reason TEXT,
            side TEXT
        )''')
        conn.commit()
        conn.close()

    def save_orders(self, orders: List[Order]) -> Dict:
        """保存订单并返回详细结果"""
        if not orders:
            return {
                'success': True,
                'saved_count': 0,
                'duplicate_count': 0,
                'duplicate_orders': []
            }
        
        conn = self._get_conn()
        c = conn.cursor()
        saved_count = 0
        duplicate_count = 0
        duplicate_orders = []
        
        for order in orders:
            # 检查是否重复
            try:
                if self.is_order_duplicate(order):
                    duplicate_count += 1
                    order_id = order.order_id
                    duplicate_orders.append(order_id)
                    continue
            except Exception as e:
                print(f"Error checking order duplicate: {e}")
                continue
                
            try:
                c.execute('''INSERT INTO orders VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', (
                    str(order.order_id),
                    str(order.symbol),
                    str(order.side),
                    str(order.order_type),
                    float(order.quantity) if order.quantity is not None else None,
                    float(order.filled) if order.filled is not None else None,
                    float(order.quantity_left) if order.quantity_left is not None else None,
                    float(order.limit_price) if order.limit_price is not None else None,
                    float(order.stop_price) if order.stop_price is not None else None,
                    float(order.avg_fill_price) if order.avg_fill_price is not None else None,
                    str(order.status),
                    order.open_time.isoformat() if isinstance(order.open_time, datetime) else str(order.open_time),
                    order.close_time.isoformat() if isinstance(order.close_time, datetime) else str(order.close_time),
                    str(order.duration),
                    float(order.commission) if order.commission is not None else None,
                    order.expiration.isoformat() if order.expiration is not None and isinstance(order.expiration, datetime) else (str(order.expiration) if order.expiration is not None else None),
                    float(order.multiplier) if order.multiplier is not None else None
                ))
                saved_count += 1
            except Exception as e:
                print(f"Error saving order {order.order_id}: {e}")
                
        conn.commit()
        conn.close()
        
        return {
            'success': True,
            'saved_count': saved_count,
            'duplicate_count': duplicate_count,
            'duplicate_orders': duplicate_orders
        }

    def save_trades(self, trades: list) -> None:
        if not trades:
            return
        conn = self._get_conn()
        c = conn.cursor()
        fields = [
            "trade_id", "symbol", "open_datetime", "open_price", "open_qty", "close_datetime", "close_price", "close_qty",
            "direction", "open_order_type", "trade_type", "point_profit_per_lot", "total_points", "gross_profit", "net_profit",
            "commission", "holding_time", "nth_trade", "session", "max_floating_profit_points", "max_floating_profit_dollars",
            "max_floating_loss_points", "max_floating_loss_dollars", "entry_reason", "exit_reason", "stop_loss_price",
            "stop_loss_reason", "take_profit_price", "take_profit_reason", "side"
        ]
        placeholders = ','.join(['?'] * len(fields))
        sql = f"INSERT INTO trades ({','.join(fields)}) VALUES ({placeholders})"
        def to_sqlite_value(val):
            if isinstance(val, decimal.Decimal):
                return float(val)
            return val
        try:
            for trade in trades:
                values = [getattr(trade, f) for f in fields]
                values = [to_sqlite_value(v) for v in values]
                c.execute(sql, values)
            conn.commit()
        except Exception as e:
            conn.rollback()
            raise e
        finally:
            conn.close()

    def get_orders(self) -> List[Order]:
        conn = self._get_conn()
        c = conn.cursor()
        c.execute('SELECT * FROM orders')
        rows = c.fetchall()
        conn.close()
        orders = []
        for row in rows:
            order = Order()
            order.order_id = row[0]
            order.symbol = row[1]
            order.side = row[2]
            order.order_type = row[3]
            order.quantity = float(row[4]) if row[4] is not None else None
            order.filled = float(row[5]) if row[5] is not None else None
            order.quantity_left = float(row[6]) if row[6] is not None else None
            order.limit_price = float(row[7]) if row[7] is not None else None
            order.stop_price = float(row[8]) if row[8] is not None else None
            order.avg_fill_price = float(row[9]) if row[9] is not None else None
            order.status = row[10]
            order.open_time = datetime.fromisoformat(row[11]) if row[11] else None
            order.close_time = datetime.fromisoformat(row[12]) if row[12] else None
            order.duration = row[13]
            order.commission = float(row[14]) if row[14] is not None else None
            order.expiration = datetime.fromisoformat(row[15]) if row[15] else None
            order.multiplier = float(row[16]) if row[16] is not None else None
            orders.append(order)
        return orders

    def get_trades(self) -> list:
        fields = [
            "trade_id", "symbol", "open_datetime", "open_price", "open_qty", "close_datetime", "close_price", "close_qty",
            "direction", "open_order_type", "trade_type", "point_profit_per_lot", "total_points", "gross_profit", "net_profit",
            "commission", "holding_time", "nth_trade", "session", "max_floating_profit_points", "max_floating_profit_dollars",
            "max_floating_loss_points", "max_floating_loss_dollars", "entry_reason", "exit_reason", "stop_loss_price",
            "stop_loss_reason", "take_profit_price", "take_profit_reason", "side"
        ]
        float_fields = [
            "open_price", "open_qty", "close_price", "close_qty", "point_profit_per_lot", "total_points", "gross_profit", "net_profit",
            "commission", "max_floating_profit_points", "max_floating_profit_dollars", "max_floating_loss_points", "max_floating_loss_dollars",
            "stop_loss_price", "take_profit_price"
        ]
        int_fields = ["nth_trade"]
        def safe_float(val):
            try:
                if val is None or val == '' or str(val).lower() == 'nan':
                    return None
                return float(val)
            except Exception:
                return None
        def safe_datetime(val):
            try:
                if val is None or val == '' or str(val).lower() == 'nan':
                    return None
                if isinstance(val, datetime):
                    return val
                return datetime.fromisoformat(val)
            except Exception:
                return None
        conn = self._get_conn()
        c = conn.cursor()
        sql = f'SELECT {",".join(fields)} FROM trades'
        c.execute(sql)
        rows = c.fetchall()
        conn.close()
        trades = []
        for row in rows:
            trade_kwargs = dict(zip(fields, row))
            for f in float_fields:
                trade_kwargs[f] = safe_float(trade_kwargs[f])
            for f in int_fields:
                trade_kwargs[f] = int(trade_kwargs[f]) if trade_kwargs[f] not in (None, '', 'nan') else None
            for dt_field in ["open_datetime", "close_datetime"]:
                trade_kwargs[dt_field] = safe_datetime(trade_kwargs[dt_field])
            
            # 创建Trade对象
            trade = Trade()
            for field, value in trade_kwargs.items():
                setattr(trade, field, value)
            trades.append(trade)
        return trades

    def is_order_duplicate(self, order: Order) -> bool:
        conn = self._get_conn()
        c = conn.cursor()
        try:
            # 获取订单属性，避免访问detached对象
            order_id = order.order_id
            symbol = order.symbol
            side = order.side
            order_type = order.order_type
            status = order.status
            filled = str(order.filled)
            avg_fill_price = str(order.avg_fill_price)
            
            c.execute('''SELECT 1 FROM orders WHERE order_id=? AND symbol=? AND side=? AND order_type=? AND status=? AND filled=? AND avg_fill_price=?''', (
                order_id, symbol, side, order_type, status, filled, avg_fill_price
            ))
            result = c.fetchone()
            return result is not None
        except Exception as e:
            print(f"Error checking order duplicate: {e}")
            return False
        finally:
            conn.close()

    def is_trade_duplicate(self, trade: Trade) -> bool:
        conn = self._get_conn()
        c = conn.cursor()
        c.execute('''SELECT 1 FROM trades WHERE open_datetime=? AND close_datetime=? AND symbol=? AND open_price=? AND close_price=?''', (
            trade.open_datetime.isoformat() if isinstance(trade.open_datetime, datetime) else str(trade.open_datetime),
            trade.close_datetime.isoformat() if isinstance(trade.close_datetime, datetime) else str(trade.close_datetime),
            trade.symbol,
            str(trade.open_price),
            str(trade.close_price)
        ))
        result = c.fetchone()
        conn.close()
        return result is not None

    def update_trade(self, trade_id: str, updates: dict) -> bool:
        """
        更新单个交易记录
        :param trade_id: 交易ID
        :param updates: 要更新的字段和值的字典
        :return: 是否成功
        """
        if not updates:
            return False
        conn = self._get_conn()
        c = conn.cursor()
        set_clause = ', '.join([f"{k}=?" for k in updates.keys()])
        values = list(updates.values())
        values.append(trade_id)
        try:
            c.execute(f"UPDATE trades SET {set_clause} WHERE trade_id=?", values)
            conn.commit()
            return c.rowcount > 0
        except Exception as e:
            print(f"Error updating trade: {e}")
            return False
        finally:
            conn.close()

    def delete_trade(self, trade_id: str) -> bool:
        """
        删除单个交易记录
        :param trade_id: 交易ID
        :return: 是否成功
        """
        conn = self._get_conn()
        c = conn.cursor()
        try:
            c.execute("DELETE FROM trades WHERE trade_id=?", (trade_id,))
            conn.commit()
            return c.rowcount > 0
        except Exception as e:
            print(f"Error deleting trade: {e}")
            return False
        finally:
            conn.close()

    def close(self):
        """关闭存储连接（SQLite3不需要显式关闭）"""
        pass
